package bsk_dac.database;

import bsk_dac.MainDACFrame;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DatabaseFacade {

    static public int getUserId(String userName) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select id from users where login='" + userName + "'";
        int user_id = -1;
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            user_id = rs.getInt("id");
        }
        return user_id;
    }

    static public String getUserName(int userId) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select login from users where id=" + userId + "";
        String userName = null;
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            userName = rs.getString("login");
        }
        return userName;
    }

    static public boolean getUserGivePriv(String userName) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select give from users where login='" + userName + "'";
        boolean user_id = false;
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            user_id = rs.getBoolean("give");
        }
        return user_id;
    }

    static public void setUserGivePriv(String userName, boolean givePermission) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "update users set give = " + givePermission + " where login='" + userName + "'";
        boolean user_id = false;
        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);
    }

    static public int getTableId(String tableName) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select id from `table` where tablename='" + tableName + "'";
        int table_id = -1;
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            table_id = rs.getInt("id");
        }
        return table_id;
    }

    static public String getTableName(int tableID) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select tablename from `table` where id=" + tableID + "";
        String tableName = null;
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            tableName = rs.getString("tablename");
        }
        return tableName;
    }

    static public void addNewUserToDB(String login, String password, boolean give) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        int lastUserId = -1;

        String query = "select MAX(id) as highestId FROM users";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            lastUserId = rs.getInt("highestId");
        }

        lastUserId++;

        query = "insert into users (id, login , pass, email, add_date, give) values (" + lastUserId + ", '" + login + "', '" + password + "', 'em@em.pl', '04.05.2011', " + give + ");";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);
    }

    static public boolean isPermissionsInDatabase(int userid, int tableid, String permissionName) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  table_permissions as tp JOIN table_has_table_permissions as thtp "
                + "ON thtp.users_id = " + userid + "  and thtp.table_id = " + tableid + " and tp.permission_name = '" + permissionName + "' and tp.id = thtp.table_permissions_id;";
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        boolean isPermission = false;
        while (rs.next()) {
            isPermission = true;
        }
        return isPermission;
    }

    static public void insertNewPermission(int userid, int tableid, String permissionName, boolean permission, boolean get) throws SQLException {
        boolean give = false;
        int table_permission_id = 0;
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;

        String query = "select MAX(id) as highestId FROM table_permissions";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            table_permission_id = rs.getInt("highestId");
        }

        table_permission_id++;


        query = "insert into table_permissions (id, permission, permission_name, give, get) "
                + "values (" + table_permission_id + ", " + permission + ", '" + permissionName + "', " + give + ", " + get + ");";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);

        query = "insert into table_has_table_permissions (table_id, table_permissions_id, users_id) values (" + tableid + ", " + table_permission_id + ", " + userid + ");";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);
    }

    static public boolean getTablePriveleges(int userid, int tableid, String permissionName) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  table_permissions as tp JOIN table_has_table_permissions as thtp "
                + "ON thtp.users_id = " + userid + "  and thtp.table_id = " + tableid + " and tp.permission_name = '" + permissionName + "' and tp.id = thtp.table_permissions_id;";
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        boolean privileges = false;
        while (rs.next()) {
            privileges = rs.getBoolean("permission");
        }
        return privileges;
    }

    static public int getTablePrivelegesId(int userid, int tableid, String permissionName) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  table_permissions as tp JOIN table_has_table_permissions as thtp "
                + "ON thtp.users_id = " + userid + "  and thtp.table_id = " + tableid + " and tp.permission_name = '" + permissionName + "' and tp.id = thtp.table_permissions_id;";
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        int id = -1;

        while (rs.next()) {
            id = rs.getInt("id");
        }
        return id;
    }

    static public void setTablePriveleges(int userid, int tableid, String permissionName, boolean permission, boolean give_perm) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        int table_permission_id = getTablePrivelegesId(userid, tableid, permissionName);
        String query = "update table_permissions set permission = " + permission + ", get = " + give_perm + " where id = " + table_permission_id + ";";
        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);
    }

    static public boolean getTableGivePriveleges(int userid, int tableid, String permissionName) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  table_permissions as tp JOIN table_has_table_permissions as thtp "
                + "ON thtp.users_id = " + userid + "  and thtp.table_id = " + tableid + " and tp.permission_name = '" + permissionName + "' and tp.id = thtp.table_permissions_id;";
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        boolean privileges = false;
        while (rs.next()) {
            privileges = rs.getBoolean("get");
        }
        return privileges;
    }

    static public void deleteUser(String userName) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        int user_id = getUserId(userName);
        String query = "SELECT table_permissions_id from table_has_table_permissions as thtp where users_id = " + user_id + " ";
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);


        query = "DELETE FROM table_has_table_permissions WHERE users_id = " + user_id + " ";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);

        int table_id = -1;
        while (rs.next()) {
            table_id = rs.getInt("table_permissions_id");
            deleteTablePermissions(table_id);
        }

        query = "DELETE FROM users where id = " + user_id + " ";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);
    }

    static public void deleteTablePermissions(int table_id) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;

        String query = "DELETE FROM table_permissions WHERE id=" + table_id + "";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);
    }

    //HISTORIA
    static public void addToHistory(int users_get_id, int users_give_id, int tp_id, String permissionName, int table_id) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "insert into history (users_get_id, users_give_id, tp_id, permission_name, table_id) "
                + "values (" + users_get_id + ", " + users_give_id + ", " + tp_id + ", '" + permissionName + "', " + table_id + ");";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);
    }

    static public ArrayList<Integer> getHistoryID(int users_give_id, String permissionName, int table_id) throws SQLException {
        ArrayList<Integer> historiesId = new ArrayList<Integer>();
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  history "
                + "where users_give_id = " + users_give_id + " and table_id = " + table_id + " and permission_name = '" + permissionName + "'";

        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            historiesId.add(rs.getInt("id"));
        }

        return historiesId;
    }

    static public ArrayList<Integer> getHistoryIDByUserGet(int users_get_id, String permissionName, int table_id) throws SQLException {
        ArrayList<Integer> historiesId = new ArrayList<Integer>();
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  history "
                + "where users_get_id = " + users_get_id + " and table_id = " + table_id + " and permission_name = '" + permissionName + "'";

        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            historiesId.add(rs.getInt("id"));
        }

        return historiesId;
    }

    static public void updateHistoryID(int old_geter, int new_user_get_id) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "update history set users_get_id = "+new_user_get_id+" "
                + "where users_get_id = "+old_geter+"";

        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);
    }

    static public void setAdmin(String newAdmin) throws SQLException{
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "update users set name='' where name='admin'";
        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);

        query = "update users set name='admin' where login='"+newAdmin+"'";
        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);
    }

    static public String getAdmin() throws SQLException{
        String adminName="";
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  users where name = 'admin'";

        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            adminName = rs.getString("login");
        }
        return adminName;
    }

    static public void setGivePriv(String userName, boolean give) throws SQLException{
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "update users set give = "+give+" where login='"+userName+"'";
        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);

    }

    static public void updateHistoryID(int old_geter, int new_user_get_id, String permissionName, int table_id, int tp_id) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "update history set users_get_id = "+new_user_get_id+", tp_id = "+tp_id + " "
                + "where users_get_id = "+old_geter+" and table_id = " + table_id + " and permission_name = '" + permissionName + "'";

        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);
    }
/*
    static public void updateHistoryID(int users_get_id, String permissionName, int table_id, int new_user_get_id) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "update history set users_get_id = "+new_user_get_id+" "
                + "where users_get_id = " + users_get_id + " and table_id = " + table_id + " and permission_name = '" + permissionName + "'";

        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);
    }
*/
    static public int getHistoryID(int users_give_id, int users_get_id, String permissionName, int table_id) throws SQLException {
        int historyId = -1;
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  history "
                + "where users_get_id = " + users_get_id + " and users_give_id = " + users_give_id + " and table_id = " + table_id + " and permission_name = '" + permissionName + "'";

        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            historyId = rs.getInt("id");
        }

        return historyId;
    }

    static public ArrayList<String> getPermissionNamesInHistory(int users_give_id, int users_get_id, int table_id) throws SQLException {
        ArrayList<String> permissions = new ArrayList<String>();

        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  history "
                + "where users_get_id = " + users_get_id + " and users_give_id = " + users_give_id + " and table_id = " + table_id;

        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            permissions.add(rs.getString("permission_name"));
        }

        return permissions;
    }

    static public void deleteHistories(ArrayList<Integer> historyID) throws SQLException {
        for (Integer his_id : historyID) {
            deleteHistory(his_id);
        }
    }

    static public void deleteHistory(int historyId) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;

        String query = "DELETE FROM history WHERE id=" + historyId + "";
        stmt = (Statement) conn.createStatement();
        stmt.execute(query);
    }

    static public int getTablePermissionIdByHistory(int historyId) throws SQLException {
        int tp_id = -1;
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  history "
                + "where id = " + historyId + ";";
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            tp_id = rs.getInt("tp_id");
        }

        return tp_id;
    }

    static public void removeAllPrivileges(int tp_id) throws SQLException {
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "update table_permissions set permission = 0, get = 0 where id = '" + tp_id + "'";
        stmt = (Statement) conn.createStatement();
        stmt.executeUpdate(query);
    }

    static public int getHistoryUserGetId(int historyId) throws SQLException {
        int user_get_id = -1;
        Connection conn = MainDACFrame.conn;
        Statement stmt = null;
        String query = "select * from  history "
                + "where id = " + historyId + ";";
        stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            user_get_id = rs.getInt("users_get_id");
        }

        return user_get_id;
    }
}
